import pymysql
import requests
from datetime import datetime
from time import mktime
import hashlib
import json


# 企查查股东
def qichacha1start():
    try:
        print(datetime.now())
        savesql('truncate touzi_gsxx_jzdc')
        codelist = getCreditCode()
        i = 0
        for code in codelist:
            code = code[0]
            print(code)
            info1 = send_request(
                'https://pro.qcc.com/api/yj/GeneralTaxPayerCheck/GetList',
                code)
            info2 = send_request(
                'https://pro.qcc.com/api/yj/ECIInfoVerify/GetInfo',
                code)
            try:
                info2 = info2['Result']
                sql = makesql(info1, info2)
                savesql(sql)
                print(code, datetime.now(), '已完成')
                i += 1
            except Exception as e:
                print(code, e)
        log = "清空表，更新" + str(i) + "条入库touzi_gsxx_jzdc"
        savesql('INSERT INTO behc_log (name, log, update_time) VALUES ("企查查股东", "' + log + '", "' + datetime.strftime(
            datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')
        gdxx()
        dwtz()
    except Exception as e:
        print(e)


def gdxx():
    savesql('truncate touzi_gdxx_jzdc')
    codelist = getCreditCode()
    num = 0
    for code in codelist:
        code0 = code[2]
        print(code0)
        try:
            info = send_request2(
                'https://pro.qcc.com/api/yj/ECIPartner/GetList', code0, 'gdxx')
            for i in info:
                r = ''
                for v in i.values():
                    r += '"' + v + '",'
                for c in code:
                    r += '"' + c + '",'
                sql = 'INSERT INTO touzi_gdxx_jzdc (KeyNo, StockName, StockType, StockPercent, ShouldCapi, ShoudDate, corpKeyNo, corpName, CreditCode) VALUES ('
                sql += r[:-1] + ');'
                savesql(sql)
                num += 1
        except:
            print(code, '没有股东')
    log = "清空表，更新" + str(num) + "条入库touzi_gdxx_jzdc"
    savesql('INSERT INTO behc_log (name, log, update_time) VALUES ("企查查股东", "' + log + '", "' + datetime.strftime(
        datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')


def dwtz():
    savesql('truncate touzi_dwtz_jzdc')
    codelist = getCreditCode()
    num = 0
    for code in codelist:
        code0 = code[2]
        print(code0)
        info = send_request2(
            'https://pro.qcc.com/api/yj/InvestmentCheck/GetList', code0,
            'dwtz')
        if info:
            for i in info:
                r = ''
                for k, v in i.items():
                    if k != 'Oper':
                        r += '"' + v + '",'
                for vo in i['Oper']:
                    r += '"' + vo + '",'
                for c in code:
                    r += '"' + c + '",'
                sql = 'INSERT INTO touzi_dwtz_jzdc (KeyNo, Name, RegistCapi, FundedRatio, ShouldCapi, StartDate, Status, Oper_KeyNo, Oper_Name, corpKeyNo, corpName, CreditCode) VALUES ('
                sql += r[:-1] + ');'
                savesql(sql)
                num += 1
    log = "清空表，更新" + str(num) + "条入库touzi_dwtz_jzdc"
    savesql('INSERT INTO behc_log (name, log, update_time) VALUES ("企查查股东", "' + log + '", "' + datetime.strftime(
        datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')


# 获取统一社会信用代码
def getCreditCode():
    sql = 'SELECT * FROM touzi_gs_jzdc'
    con = None
    cur = None
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            datas = cur.fetchall()
        else:
            print("链接失败")
    except Exception as e:
        print(e)
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    return datas


def send_request(url, searchKey):
    key = '633b8c152efd11eb990b0c42a106ce72'
    timespan = str(int(mktime(datetime.now().timetuple())))
    secretKey = '20KKVCV08SZA7XOFQ5BSS8JQUNFX3FLC'
    token = key + timespan + secretKey
    token = hashlib.md5(token.encode('utf-8')).hexdigest().upper()
    try:
        response = requests.get(
            url=url,
            params={
                "key": key,
                "searchKey": searchKey,
            },
            headers={
                "Token": token,
                "Timespan": timespan,
            },
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')
    return json.loads(response.content)


def send_request2(url, searchKey, d):
    key = '633b8c152efd11eb990b0c42a106ce72'
    timespan = str(int(mktime(datetime.now().timetuple())))
    secretKey = '20KKVCV08SZA7XOFQ5BSS8JQUNFX3FLC'
    token = key + timespan + secretKey
    token = hashlib.md5(token.encode('utf-8')).hexdigest().upper()
    try:
        response = requests.get(
            url=url,
            params={
                "key": key,
                "searchKey": searchKey,
            },
            headers={
                "Token": token,
                "Timespan": timespan,
            },
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
        result = json.loads(response.content)
        if result['Paging']:
            print(result['Paging'])
            pages = int(result['Paging']['TotalRecords']) // 10 + 1
        else:
            pages = 0
        if d == 'gdxx':
            result2 = result['Result']
        if d == 'dwtz':
            result2 = result['Result']['Data']
        if pages > 1:
            for p in range(2, pages + 1):
                response = requests.get(
                    url=url,
                    params={
                        "key": key,
                        "searchKey": searchKey,
                        "pageIndex": p,
                    },
                    headers={
                        "Token": token,
                        "Timespan": timespan,
                    },
                )
                print('page:', p)
                print('Response HTTP Status Code: {status_code}'.format(
                    status_code=response.status_code))
                result = json.loads(response.content)
                if d == 'gdxx':
                    result = result['Result']
                if d == 'dwtz':
                    result = result['Result']['Data']
                if result:
                    if len(result) > 0:
                        for r in result:
                            result2.append(r)
    except requests.exceptions.RequestException:
        print('HTTP Request failed')
    return result2


# 生成sql
def makesql(info1, info2):
    contactInfo = info2['ContactInfo']
    industry = info2['Industry']
    area = info2['Area']
    info = {
        'Employees': str(info2['Employees']),
        'Branches': str(info2['Branches']),
        'ChangeRecords': str(info2['ChangeRecords']),
        'Industry_IndustryCode': industry['IndustryCode'],
        'Industry_Industry': industry['Industry'],
        'Industry_SubIndustryCode': industry['SubIndustryCode'],
        'Industry_SubIndustry': industry['SubIndustry'],
        'Industry_MiddleCategoryCode': industry['MiddleCategoryCode'],
        'Industry_MiddleCategory': industry['MiddleCategory'],
        'Industry_SmallCategoryCode': industry['SmallCategoryCode'],
        'Industry_SmallCategory': industry['SmallCategory'],
        'Area_Province': area['Province'],
        'Area_City': area['City'],
        'Area_County': area['County'],
        'RevokeInfo': str(info2['RevokeInfo']),
        'InsuredCount': info2['RevokeInfo'],
        'EnglishName': info2['RevokeInfo'],
        'PersonScope': info2['RevokeInfo'],
        'IXCode': info2['RevokeInfo'],
        'TagList': str(info2['RevokeInfo']),
        'ARContactList': str(info2['ARContactList']),
        'EconKindCodeList': str(info2['EconKindCodeList']),
        'KeyNo': info2['KeyNo'],
        'Name': info2['Name'],
        'No': info2['No'],
        'BelongOrg': info2['BelongOrg'],
        'OperId': info2['OperId'],
        'OperName': info2['OperName'],
        'StartDate': info2['StartDate'],
        'EndDate': info2['EndDate'],
        'Status': info2['Status'],
        'Province': info2['Province'],
        'UpdatedDate': info2['UpdatedDate'],
        'CreditCode': info2['CreditCode'],
        'RegistCapi': info2['RegistCapi'],
        'EconKind': info2['EconKind'],
        'Address': info2['Address'],
        'Scope': info2['Scope'],
        'TermStart': info2['TermStart'],
        'TeamEnd': info2['TeamEnd'],
        'UpdatedDate': info2['UpdatedDate'],
        'CheckDate': info2['CheckDate'],
        'OrgNo': info2['OrgNo'],
        'IsOnStock': info2['IsOnStock'],
        'StockNumber': info2['StockNumber'],
        'StockType': info2['StockType'],
        'OriginalName': str(info2['OriginalName']),
        'ImageUrl': info2['ImageUrl'],
        'EntType': info2['EntType'],
        'RecCap': info2['RecCap'],
    }
    try:
        partners = info2['Partners'][0]
        info['Partners_KeyNo'] = partners['KeyNo']
        info['Partners_StockName'] = partners['StockName']
        info['Partners_StockType'] = partners['StockType']
        info['Partners_StockPercent'] = partners['StockPercent']
        info['Partners_ShouldCapi'] = partners['ShouldCapi']
        info['Partners_ShoudDate'] = partners['ShoudDate']
        info['Partners_InvestType'] = partners['InvestType']
        info['Partners_InvestName'] = partners['InvestName']
        info['Partners_RealCapi'] = partners['RealCapi']
        info['Partners_CapiDate'] = partners['CapiDate']
        info['Partners_TagsList'] = str(partners['TagsList'])
        info['Partners_FinalBenefitPercent'] = partners['FinalBenefitPercent']
        if partners['RelatedProduct']:
            info['Partners_RelatedProduct_Id'] = partners['RelatedProduct'][
                'Id']
            info['Partners_RelatedProduct_Name'] = partners['RelatedProduct'][
                'Name']
            info['Partners_RelatedProduct_Round'] = partners['RelatedProduct'][
                'Round']
            info['Partners_RelatedProduct_FinancingCount'] = partners[
                'RelatedProduct']['FinancingCount']
        else:
            info['Partners_RelatedProduct_Id'] = ''
            info['Partners_RelatedProduct_Name'] = ''
            info['Partners_RelatedProduct_Round'] = ''
            info['Partners_RelatedProduct_FinancingCount'] = ''
        if partners['RelatedOrg']:
            info['Partners_RelatedOrg_Id'] = partners['RelatedOrg']['Id']
            info['Partners_RelatedOrg_Name'] = partners['RelatedOrg']['Name']
        else:
            info['Partners_RelatedOrg_Id'] = ''
            info['Partners_RelatedOrg_Name'] = ''
    except:
        info['Partners_KeyNo'] = ''
        info['Partners_StockName'] = ''
        info['Partners_StockType'] = ''
        info['Partners_StockPercent'] = ''
        info['Partners_ShouldCapi'] = ''
        info['Partners_ShoudDate'] = ''
        info['Partners_InvestType'] = ''
        info['Partners_InvestName'] = ''
        info['Partners_RealCapi'] = ''
        info['Partners_CapiDate'] = ''
        info['Partners_TagsList'] = ''
        info['Partners_FinalBenefitPercent'] = ''

        info['Partners_RelatedProduct_Id'] = ''
        info['Partners_RelatedProduct_Name'] = ''
        info['Partners_RelatedProduct_Round'] = ''
        info['Partners_RelatedProduct_FinancingCount'] = ''

        info['Partners_RelatedOrg_Id'] = ''
        info['Partners_RelatedOrg_Name'] = ''
    try:
        info1 = info1['Result']['Data'][0]
        info['Data_TaxpayerType'] = info1['TaxpayerType']
        info['Data_BelongOrg'] = info1['BelongOrg']
        info['Data_DateFrom'] = info1['DateFrom']
        info['Data_DateTo'] = info1['DateTo']
    except:
        info['Data_TaxpayerType'] = ''
        info['Data_BelongOrg'] = ''
        info['Data_DateFrom'] = ''
        info['Data_DateTo'] = ''
    if contactInfo['WebSite']:
        info['ContactInfo_WebSite_Name'] = contactInfo['WebSite'][0]['Name']
        info['ContactInfo_WebSite_NameUrl'] = contactInfo['WebSite'][0]['Url']
        info['ContactInfo_PhoneNumber'] = contactInfo['PhoneNumber']
        info['ContactInfo_Email'] = contactInfo['Email']
    else:
        info['ContactInfo_WebSite_Name'] = ''
        info['ContactInfo_WebSite_NameUrl'] = ''
        info['ContactInfo_PhoneNumber'] = contactInfo['PhoneNumber']
        info['ContactInfo_Email'] = contactInfo['Email']

    sql = 'INSERT INTO touzi_gsxx_jzdc ('
    sql2 = ') VALUES ('
    for k, v in info.items():
        sql += '' + k + ','
        sql2 += '"' + str(v) + '",'
    sql = sql[:-1]
    sql2 = sql2[:-1]
    return sql + sql2 + ');'


# 执行sql
def savesql(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
    except Exception as e:
        result = e
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    print(result)
    return result


qichacha1start()
